package com.example.tingsugar.mapper;

import com.example.tingsugar.bean.AlbumAvgPlayDurationBean;
import com.example.tingsugar.bean.HourPlayDurationBean;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @Author lzc
 * @Date 2023/4/16 15:21
 */
public interface UserPlayMapper {
    @Select("SELECT\n" +
        "    album_id album,\n" +
        "    sum(play_during) / countDistinct(user_id) avg_play_duration\n" +
        "FROM dws_user_play_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY album_id")
    List<AlbumAvgPlayDurationBean> statsPlayDuration(Integer date);
    
    @Select("SELECT sum(play_during) / countDistinct(user_id)\n" +
        "FROM dws_user_play_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}")
    BigDecimal avgDuration(Integer date);
    
    @Select("SELECT\n" +
        "    toHour(stt) AS hour,\n" +
        "    sum(play_during) / countDistinct(user_id) / 1000 / 60 AS avg_play_duration\n" +
        "FROM dws_user_play_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY toHour(stt)")
    List<HourPlayDurationBean> statsHourPlay(Integer date);
}
